package 数据库操作;

import org.junit.Test;
import 数据库连接.jdbc_test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @作者:侯国威
 * @时间: 2020/4/14/0:59
 * @邮箱:1045077189@qq.com PrepateStatement替换Statement对数据的增删改查
 * 增删改； 查(返回数据)
 */
public class PrepateStatement_Insertdate {

    //添加数据
    @Test
    public void testInsert() throws SQLException {

        //方式二:数据库需要加载的信息放到配置文件中

        Connection connection = null;

        PreparedStatement preparedStatement = null;

        try {

            //1、读取配置文件的信息

            InputStream is = jdbc_test.class.getClassLoader().getResourceAsStream("jdbc.properties");

            Properties properties = new Properties();

            assert is != null;

            properties.load(is);

            String user = properties.getProperty("user");

            String password = properties.getProperty("password");

            String url = properties.getProperty("url");

            String dirver = properties.getProperty("dirver");
            //2、加载驱动
            Class.forName(dirver);
            //3、获取连接
            connection = DriverManager.getConnection(url, user, password);

            System.out.println("数据库连接成~~~~~~~");

            //4、预编译sql语句返回PrepateStatement

            String sql = "insert into student.star(id,name, money, age, weight, height) VALUES (6,'林举',2500,23,146,176)";

            preparedStatement = connection.prepareStatement(sql);
            //5、执行
            preparedStatement.execute();

            System.out.println("添加成功");

        } catch (IOException | ClassNotFoundException e) {

            e.printStackTrace();

        } finally {

            //6、资源关闭

            assert preparedStatement != null;


            preparedStatement.close();

            connection.close();
        }

    }

    //删除数据
    @Test
    public void testDelet() throws SQLException, ClassNotFoundException, IOException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        //加载数据文件，引入数据流
        try {
            InputStream inputStream = PrepateStatement_Insertdate.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            assert inputStream != null;
            properties.load(inputStream);
            //加载 数据
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String dirver = properties.getProperty("dirver");

            //记载驱动
            Class.forName(dirver);
            //获取连接
            connection = DriverManager.getConnection(url, user, password);

            System.out.println("连接成功!!!!!");

            String sql = "delete from student.star where id=5";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.execute();
            System.out.println("删除成功");
        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

        assert preparedStatement != null;
        preparedStatement.close();
        connection.close();

    }


    //修改数据
    @Test
    public void Updata() throws SQLException {
        //1、引入数据流

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            InputStream inputStream = PrepateStatement_Insertdate.class.getClassLoader().getResourceAsStream("jdbc.properties");

            Properties properties = new Properties();
            //2、加载数据
            assert inputStream != null;

            properties.load(inputStream);

            String user = properties.getProperty("user");

            String password = properties.getProperty("password");

            String url = properties.getProperty("url");

            String dirver = properties.getProperty("dirver");
            //3、加载驱动
            Class.forName(dirver);

            connection = DriverManager.getConnection(url, user, password);

            //4、预编译sql
            String sql = "update student.star set money=1314,age=23,weight=160, height=180 where id=4";

            preparedStatement = connection.prepareStatement(sql);

            //5、执行sql
            preparedStatement.execute();

            System.out.println("操作成功");

        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            assert connection != null;
            connection.close();
            assert preparedStatement != null;
            preparedStatement.close();
        }


    }
}
